﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class Class
    {
        public String Subject_id { get; set; }
        public String Class_id { get; set; }
        public int Class_vacancy { get; set; }

        public static List<Class> GetClass()
        {
            List<Class> results = new List<Class>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Class";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Class c = new Class();
                    c.Subject_id= (String)dr["subject_id"];
                    c.Class_id = (String)dr["class_id"];
                    c.Class_vacancy = (int)dr["class_vacancy"];

                    results.Add(c);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertClass(Class c)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Class (subject_id, class_id, class_vacancy) VALUES (@sid, @cid, @vacancy)";
                comm.Parameters.AddWithValue("@sid", c.Subject_id);
                comm.Parameters.AddWithValue("@cid", c.Class_id);
                comm.Parameters.AddWithValue("@vacancy", c.Class_vacancy);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        //Retrieve all classes of a subject
        public static List<Class> RetrieveClass(String subject_id)
        {
            List<Class> results = new List<Class>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Class WHERE subject_id = @id";
                comm.Parameters.AddWithValue("@id", subject_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Class c = new Class();
                    c.Subject_id = (String)dr["subject_id"];
                    c.Class_id = (String)dr["class_id"];
                    c.Class_vacancy = (int)dr["class_vacancy"];
                    results.Add(c);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

    }
}